*** this script extracts direct owners for a specified firm list from JSCReg

* load the list of firms to search owners
use for_JSCReg_owner_search.dta, clear
contract okpo
drop _freq

* merge with JSCReg, keep all Ukrainian corporate owners 
merge 1:m okpo using JSCReg_working.dta
keep if _merge==3
drop if O_URSTAT == "Фiзичнi особи резиденти" | O_URSTAT == "Фiз.особа-власник" | O_URSTAT=="Власник - фiзична особа" | O_URSTAT == "Фiзичнi особи нерезиденти"
drop if O_NAME=="Фiзичнi особи резиденти" | O_NAME=="Фiзичнi особи нерезиденти" | O_NAME=="Власник - фiзична особа" | O_NAME=="ФIЗИЧНI ОСОБИ РЕЗИДЕНТИ" | O_NAME=="ФIЗИЧНI ОСОБИ НЕРЕЗИДЕНТИ" | O_NAME=="Фiзична особа" | O_NAME=="ФIЗ.ОСОБА" | O_NAME=="Фiзична особа резидент"
drop _merge

* rename country variable
rename country_code_ISO country 
 
* drop Ukrainian owners with missing identifier codes
drop if country == 804 & (investor_code==0 | missing(investor_code))

* create temporary codes for all foreign owners
* later these codes will be replaced with unique codes
* for the purpose of building the owner network
replace investor_code = 1234567890 if country != 804

* add other firm variables
merge m:m okpo using for_JSCReg_owner_search.dta
keep if _merge==3
drop _merge
* type of entity (1 - corporate)
gen entity = 1
* rename variables
rename investor_code id
rename O_NAME name
rename okpo prev_id
gen owner = 1
label var prev_id "id of entity from previous step"
label var prev_name "name of entity from previous step"
label var prev_country "country of entity from previous step"
label var prev_entity "type of entity from previous step"
label var owner "owner of previous step entity"
drop D_POST D_VNES O_ADDRESS
order name entity country owner prev_id prev_name prev_entity prev_country Delo_UP_firm O_URSTAT, after(id)
save corp_owners_from_JSCReg.dta, replace

* find firms that have no owners in JSCReg (for subsequent search in SReg)
* (will search for corporate and individual owners, Ukrainian and foreign)
* load the list of firms to search owners
use for_JSCReg_owner_search.dta, clear
contract okpo
drop _freq

* merge with JSCReg, keep all firms for which there are no owners 
merge 1:m okpo using JSCReg_working.dta
keep if _merge==1
keep okpo

* create data frame for subsequent owner search in SReg
merge 1:m okpo using for_JSCReg_owner_search.dta
keep if _merge==3
drop _merge
* add dummies to search for corporate and individual owners
expand 2
gen entity = 1
replace entity = 0 if _n > _N/2
save for_SReg_owner_search_all_owners.dta, replace

* add firms that are in JSCReg, but have individual owners
* load the list of firms to search owners
use for_JSCReg_owner_search.dta, clear
contract okpo
drop _freq

* merge with JSCReg, keep firms that have individual owners
merge 1:m okpo using JSCReg_working.dta
keep if _merge==3
keep if O_URSTAT == "Фiзичнi особи резиденти" | O_URSTAT == "Фiз.особа-власник" | O_URSTAT=="Власник - фiзична особа" | O_URSTAT == "Фiзичнi особи нерезиденти" | O_NAME=="Фiзичнi особи резиденти" | O_NAME=="Фiзичнi особи нерезиденти" | O_NAME=="Власник - фiзична особа" | O_NAME=="ФIЗИЧНI ОСОБИ РЕЗИДЕНТИ" | O_NAME=="ФIЗИЧНI ОСОБИ НЕРЕЗИДЕНТИ" | O_NAME=="Фiзична особа" | O_NAME=="ФIЗ.ОСОБА" | O_NAME=="Фiзична особа резидент"
if _N > 0 {
* country codes
rename country_code_ISO country 

* type of entity (0 - individual)
gen entity = 0

* unless there are no firms, save data frame to search for individual owners in SReg
if _N > 0 {
rename country owner_country
contract okpo entity owner_country
drop _freq
merge m:m okpo using for_JSCReg_owner_search.dta
keep if _merge==3
drop _merge
save for_SReg_owner_search_ind_owners.dta, replace
}
}
